package com.ruoyi.cases.utils.excel;

import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 生成excel视图，xls格式 可用excel工具打开或者保存 由ViewController的return new ModelAndView(viewExcel, model)生成
 */
public class ExcelExportToFileUrlComplexSepcial {

    /**
     * 创建Excel
     *
     * @param model    模板 fileName 文件名称 excelTitle excel标题 excelTitleClos excel标题合并列数 upNoFrame:表格上特殊文字 [[行号,列号(&分隔),文字(&分隔),合并单元格(起始行,结束行,起始列,结束列),行高(可不写)]]
     *                 downNoFrame:表格下特殊文字[[行号,列号(&分隔),文字(&分隔),合并单元格(起始行,结束行,起始列,结束列),行高(可不写)]] titleStarRows:表头起始行数 (数字) titleRows:数据起始行数(数字) titles0~titles+titleRows 表头数据(字符串[])
     *                 handClum:合并单元格(字符串[起始行,结束行,起始列,结束列]) scribing:col1,row1,col2,col2(数字[]) names 数据字段 list 数据
     * @param workbook Excel属性
     * @param request  请求
     * @param response 请求
     * @throws Exception 异常
     */
    private void gotoCreateExcel(Map model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response, List<String[][]> downList) throws Exception {
        String excelName =
            model.get("fileName") + "_" + DateUtils.dateTimeNow("yyyyMMddHHmmss") + ".xls";
        String fileType = String.valueOf(model.get("fileName"));
        // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
        response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");
        String agent = request.getHeader("USER-AGENT");
        excelName = java.net.URLEncoder.encode(excelName, "UTF8");
        response.setHeader("Content-Disposition", "attachment; filename=" + excelName);
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        List<Map<String, String>> stuList = (List<Map<String, String>>) model.get("list");
        int index = 65520;
        int size = stuList.size();
        if (size > index) {
            int count = (size / index) + (size % index > 0 ? 1 : 0);
            for (int i = 1; i <= count; i++) {
                List<Map<String, String>> l = new ArrayList<Map<String, String>>();
                if (stuList.size() >= (i * index)) {
                    l = stuList.subList(index * (i - 1), index * i);
                } else {
                    l = stuList.subList(index * (i - 1), size);
                }
                if (l.size() > 0) {
                    addSheet(model, workbook, l, downList == null ? null : downList.get(i - 1));
                }
            }

        } else {
            // ExceptionUtils.serviceException("太多了");
            addSheet(model, workbook, stuList, downList == null ? null : downList.get(0));
        }
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();

    }

    private void addSheet(Map model, Workbook workbook, List<Map<String, String>> stuList, String[][] downNoFrame) {
        // 产生Excel表头
        HSSFSheet sheet = (HSSFSheet) workbook.createSheet();

        //设置列宽
        String columnWidths = (String) model.get("columnWidth");
        if (StringUtils.isNotBlank(columnWidths)) {
            String[] strings = columnWidths.split("&");
            for (String col : strings) {
                String[] split = col.split(",");
                sheet.setColumnWidth(Short.valueOf(split[0]), Integer.parseInt(split[1]) * 256);

            }
        }

        //excel标题
        String excelTitle = (String) model.get("excelTitle");
        String excelTime = (String) model.get("excelTime");
        int excelTitleClos =
            null != model.get("excelTitleClos") ? (int) model.get("excelTitleClos") : 0;
        //表格上特殊文字 [[行号,列号(&分隔),文字(&分隔),合并单元格(起始行,结束行,起始列,结束列)]]
        String excelTitleFontSize1 = (String) model.get("excelTitleFontSize");

        //字体处理方式
        Short excelTitleFontSize = Short
            .valueOf(StringUtils.isNotBlank(excelTitleFontSize1) ? excelTitleFontSize1 : "22");

        Font ztFont = getFont(workbook, excelTitleFontSize);
//        ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗

        //             ztFont.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
        //              ztFont.setStrikeout(true);                  // 是否添加删除线

        HSSFRow header = null;// 创建表头
        if (null != excelTitle) {
            //----------------标题样式---------------------
            HSSFCellStyle titleStyle = (HSSFCellStyle) workbook.createCellStyle();        //标题样式
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            titleStyle.setFont(ztFont);

            header = sheet.createRow(0);
            header.setHeight((short) 600);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, excelTitleClos));
            HSSFCell cell = header.createCell(0);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(excelTitle);
        }
        if(null != excelTime){
            HSSFCellStyle titleStyle = (HSSFCellStyle) workbook.createCellStyle();        //标题样式
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

             excelTitleFontSize = Short
                .valueOf(StringUtils.isNotBlank(excelTitleFontSize1) ? excelTitleFontSize1 : "10");

            ztFont = getFont(workbook, excelTitleFontSize);
//            ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
            titleStyle.setFont(ztFont);

            header = sheet.createRow(1);
            header.setHeight((short) 400);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, excelTitleClos));
            HSSFCell cell = header.createCell(0);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(excelTime);
        }

        //字体
        String titleFontSizeStr = (String) model.get("titleFontSize");
        Short titleFontSize = Short
            .valueOf(StringUtils.isNotBlank(titleFontSizeStr) ? titleFontSizeStr : "12");
        Font tFont = getFont(workbook, excelTitleFontSize);

        tFont.setFontHeightInPoints(titleFontSize);
        CellStyle frameStyle = workbook.createCellStyle();
        frameStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        frameStyle.setFont(tFont);
        String[][] upNoFrame = (String[][]) model.get("upNoFrame");

        //[[行号,列号(&分隔),文字(&分隔),合并单元格(起始行,结束行,起始列,结束列)]]
        if (null != upNoFrame) {
            for (int i = 0; i < upNoFrame.length; i++) {
                String[] strings = upNoFrame[i];
                header = sheet.createRow(Integer.parseInt(strings[0]));
                String[] clos = strings[1].split("&");
                String[] cloTitles = strings[2].split("&");
                String[] cloHand = strings[3].split("&");
                String height = null;
                if (strings.length == 5) {
                    height = strings[4];
                }
                if (null != height) {
                    header.setHeight(Short.parseShort(height));
                }
                for (int c = 0; c < clos.length; c++) {
                    String[] cloHands = cloHand[c].split(",");
                    sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(cloHands[0]),
                        Integer.parseInt(cloHands[1]), Integer.parseInt(cloHands[2]),
                        Integer.parseInt(cloHands[3])));
                    HSSFCell cell = header.createCell(Integer.parseInt(clos[c]));
                    cell.setCellStyle(frameStyle);
                    cell.setCellValue(cloTitles[c]);
                }
            }
        }

        //表格下特殊文字 [[行号,列号(&分隔),文字(&分隔),合并单元格(起始行,结束行,起始列,结束列)]]
        downNoFrame = downNoFrame == null ? (String[][]) model.get("downNoFrame") : downNoFrame;
        //[[行号,列号(&分隔),文字(&分隔),合并单元格(起始行,结束行,起始列,结束列)]]
        if (null != downNoFrame) {
            for (int i = 0; i < downNoFrame.length; i++) {
                String[] strings = downNoFrame[i];
                header = sheet.createRow(Integer.parseInt(strings[0]));
                String[] clos = strings[1].split("&");
                String[] cloTitles = strings[2].split("&");
                String[] cloHand = strings[3].split("&");
                String height = null;
                if (strings.length == 5) {
                    height = strings[4];
                }
                if (null != height) {
                    header.setHeight(Short.parseShort(height));
                }
                for (int c = 0; c < clos.length; c++) {
                    String[] cloHands = cloHand[c].split(",");
                    sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(cloHands[0]),
                        Integer.parseInt(cloHands[1]), Integer.parseInt(cloHands[2]),
                        Integer.parseInt(cloHands[3])));
                    HSSFCell cell = header.createCell(Integer.parseInt(clos[c]));
                    cell.setCellStyle(frameStyle);
                    cell.setCellValue(cloTitles[c]);
                }
            }
        }
        CellStyle titleCellStyle = getCellStyle(workbook);

        //表头行数
        int titleRows = (int) model.get("titleRows");
        //表头起始行数
        int titleStarRows =
            null != model.get("titleStarRows") ? (int) model.get("titleStarRows") : 0;
        for (int i = titleStarRows; i < titleRows; i++) {

            header = sheet.createRow(i);
            header.setHeight((short) 400);
            // 产生标题列  //名称
            String[] titles = (String[]) model.get("titles" + i);
            for (int n = 0; n < titles.length; n++) {
                HSSFCell cell = header.createCell(n);
                if (titles[n].contains("scribing")) {
                    //判断是否有斜线的如果有斜线那么格式特殊设置
                    CellStyle h = workbook.createCellStyle();
                    // 水平方bai向的对齐方du式zhi
                    h.setAlignment(HorizontalAlignment.CENTER);
                    // 垂直方dao向的对齐方式
                    h.setVerticalAlignment(VerticalAlignment.CENTER);
                    //字符串
                    h.setDataFormat(HSSFDataFormat.getBuiltinFormat("TEXT"));
                    //上边框
//                    h.setBorderTop(HSSFCellStyle.BORDER_THIN);
                    //左边框
//                    h.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    h.setWrapText(true);
                    h.setFont(tFont);

                    cell.setCellStyle(h);
                    cell.setCellValue(new HSSFRichTextString(titles[n].replace("scribing", "")));
                } else {
                    titleCellStyle.setFont(tFont);
                    cell.setCellStyle(titleCellStyle);
                    cell.setCellValue(titles[n]);

                }
            }
        }
        //合并单元格
        String[] handClum = (String[]) model.get("handClum");
        // 动态合并单元格
        if (null != handClum) {
            for (int i = 0; i < handClum.length; i++) {
                // sheet.autoSizeColumn(i, true);
                String[] temp = handClum[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
        }
        //划线 起始行 结束行
        int[] scribing = (int[]) model.get("scribing");
        //划线
        if (null != scribing) {
            sheet.setColumnWidth((short) scribing[0], 15 * 256);
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short) scribing[0],
                scribing[1],
                (short) scribing[2], scribing[3]);
            HSSFSimpleShape shape1 = patriarch.createSimpleShape(a);
            shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
            shape1.setLineStyle(HSSFSimpleShape.LINESTYLE_SOLID);
        }
        String[] names = (String[]) model.get("names");//字段

        CellStyle cellStyle = getCellStyle(workbook);
        Font font = getFont(workbook, (short) 12);
        cellStyle.setFont(font);
        ExcelExportSepcial.addCell(sheet, names, cellStyle, titleRows, stuList);
    }

    /**
     * 获取字体样式
     *
     * @param workbook           excel
     * @param excelTitleFontSize 字体大小
     * @return 字体样式
     */
    private Font getFont(Workbook workbook, Short excelTitleFontSize) {
        Font ztFont = workbook.createFont();
        ztFont.setItalic(false);                     // 设置字体为斜体字
        ztFont.setColor(Font.COLOR_NORMAL);            // 将字体设置为“红色”
        ztFont.setFontHeightInPoints(excelTitleFontSize);    // 将字体大小设置为16
        ztFont.setFontName("宋体");             // 将“宋体”字体应用到当前单元格上
        return ztFont;
    }

    /**
     * 格式
     *
     * @param workbook 格式数据
     * @return
     */
    private CellStyle getCellStyle(Workbook workbook) {
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("TEXT"));//字符串
//        titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
//        titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
//        titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//        titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        return titleCellStyle;
    }

    /**
     * 创建Excel
     *
     * @param map                 map
     * @param httpServletRequest  请求
     * @param httpServletResponse 请求
     * @throws Exception 异常
     */
    public void buildExcelDocument(Map<String, Object> map, HttpServletRequest httpServletRequest,
        HttpServletResponse httpServletResponse) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        gotoCreateExcel(map, workbook, httpServletRequest, httpServletResponse, null);
    }

    /**
     * 创建Excel
     *
     * @param map                 map
     * @param httpServletRequest  请求
     * @param httpServletResponse 请求
     * @throws Exception 异常
     */
    public void buildExcelDocument(Map<String, Object> map, HttpServletRequest httpServletRequest,
        HttpServletResponse httpServletResponse, List<String[][]> downList) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        gotoCreateExcel(map, workbook, httpServletRequest, httpServletResponse, (downList!=null&&downList.size()>0)?downList:null);
    }
}
